set spark.sql.crossJoin.enabled=true;
with new_net as (
    select distinct network_code    as agent_code,
                    network_name    as agent_name,
                    bs_network_code as bs_agent_code,
                    bs_network_name as bs_agent_name
    from jms_dim.dim_sqs_region_agent_config_base
    where report_type = 3
),
     accept as (
         select dt
              , accept_proxy_code                                       as proxy_code
              , 0                                                       as work_cnt           -- 呼叫中心
              , 0                                                       as project_cnt        -- 平台
              , sum(if(work_type = '邮政工单', 1, 0))                       as appeal_cnt         -- 邮政
              , 0                                                       as claim_cnt
              , sum(if(work_type = '邮政工单' and duty_type = '第三方', 1, 0)) as third_platform_cnt --三方
              , 0                                                       as transfer_cnt_total
              , sum(if(work_type = '邮政工单' and duty_type = '网点', 1, 0))  as site_cnt
              , 0                                                       as taking_cnt
              , 0                                                       as arrival_cnt
         from jms_dm.dm_waybill_customer_complaint_detail_new_dt
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}'
           and is_delete = 2 --未删除
           and accept_proxy_code <> '888888'
         group by dt, accept_proxy_code
     ),
-- 责任网点-代理区
     -- 代理区日理赔客诉量
     duty as (
         select dt
              , duty_proxy_code                                                             as proxy_code
              , sum(if(work_type = '普通工单', 1, 0))                                           as work_cnt
              , sum(if(work_type = '平台工单', 1, 0))                                           as project_cnt
              , 0                                                                           as appeal_cnt
              , sum(if(work_type = '理赔工单', 1, 0))                                           as claim_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '第三方', 1, 0))  as third_platform_cnt
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '转运中心', 1, 0)) as transfer_cnt_total
              , sum(if(work_type in ('平台工单', '普通工单', '理赔工单') and duty_type = '网点', 1, 0))   as site_cnt
              , 0                                                                           as taking_cnt
              , 0                                                                           as arrival_cnt
         from jms_dm.dm_waybill_customer_complaint_detail_new_dt
         where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
           and dt <= '{{ execution_date | cst_ds }}'
           and is_delete = 2 --未删除
           and duty_proxy_code <> '888888'
         group by dt, duty_proxy_code
     ),
     proxy_merge as (
         -- 代理区网点收件量-同一运单在同一天只记作一次
         select dt,
                -- null      as duty_proxy_code,
                if(new_net.bs_agent_code is not null, new_net.agent_code, merges.agent_code) as proxy_code,
                -- max(agent_name) as proxy_name,
                0                                                                            as work_cnt,
                0                                                                            as project_cnt,
                0                                                                            as appeal_cnt,
                0                                                                            as claim_cnt,

                0                                                                            as third_platform_cnt,
                0                                                                            as transfer_cnt_total,
                0                                                                            as site_cnt,
                sum(if(busness_op_type = 1, proxy_bill_cnt, 0))                              as taking_cnt,
                sum(if(busness_op_type = 2, proxy_bill_cnt, 0))                              as arrival_cnt
         from (
                  select net.agent_code,
                         max(net.agent_name) as agent_name,
                         sum(tak_cnt)        as proxy_bill_cnt,
                         1                   as busness_op_type,
                         dt
                  from ( -- 收件量 取 yl_oms_oms_waybill 表 按pick_network_code 分组汇总
                           select pick_network_code,
                                  count(1) as tak_cnt,
                                  dt
                           from (
                                    select waybill_no,
                                           pick_network_code,
                                           dt,
                                           if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                                    from (
                                             select waybill_no
                                                  , pick_network_code
                                                  , dt
                                                  , substr(input_time, 1, 10) as input_day
                                             from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
                                             where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                               and dt <= '{{ execution_date | cst_ds }}'
                                               and substr(input_time, 1, 10) >=
                                                   concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                               and substr(input_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                                         ) a
                                             left join
                                         (
                                             select del_network_code, create_day
                                             from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                                             where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                               and dt <= '{{ execution_date | cst_ds }}'
                                               and del_network_code is not null
                                               and create_day is not null
                                             group by del_network_code, create_day
                                         ) b
                                         on nvl(a.pick_network_code, 'test') = b.del_network_code
                                             and nvl(a.input_day, '9999-12-01') = b.create_day
                                ) tt
                           where fenmu_tichu_flag <> 1
                           group by pick_network_code, dt
                       ) taking
                           left join
                       jms_dim.dim_network_whole_massage net
                       on pick_network_code = net.code
                  where net.network_type = 6
                  group by net.agent_code, dt

                  union
                  -- 到件量 取tab_barscan_sitearrival 表 按单号，扫描网点，扫描时间取最后一条数据，支取JT开头的单号，单号长度<=15的数据
                  select net.agent_code,
                         max(net.agent_name) as agent_name,
                         count(waybill_no)   as proxy_bill_cnt,
                         2                   as busness_op_type,
                         dt
                  from (
                           select scan_site_code,
                                  waybill_no,
                                  dt,
                                  if(b.del_network_code is not null, 1, 0) as fenmu_tichu_flag
                           from (
                                    select scan_site_code
                                         , waybill_no
                                         , date(scan_time) as                                                                     scan_day
                                         , date(scan_time) as                                                                     dt
                                         , row_number()
                                            over (partition by waybill_no,scan_site_code,date(scan_time) order by scan_time desc) rnk
                                    from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
                                    where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                      and dt <= '{{ execution_date | cst_ds }}'
                                      and substr(scan_time, 1, 10) >=
                                          concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                      and substr(scan_time, 1, 10) <= '{{ execution_date | cst_ds }}'
                                      and waybill_no like 'JT%'
                                      and length(waybill_no) <= 15
                                ) a
                                    left join
                                (
                                    select del_network_code, create_day
                                    from jms_dm.dm_waybill_customer_complaint_detail_new_dt
                                    where dt >= concat(substr('{{ execution_date | cst_ds }}', 1, 7), '-01')
                                      and dt <= '{{ execution_date | cst_ds }}'
                                      and del_network_code is not null
                                      and create_day is not null
                                    group by del_network_code, create_day
                                ) b
                                on nvl(a.scan_day, '9999-12-01') = b.create_day
                                    and nvl(a.scan_site_code, 'test') = b.del_network_code
                           where a.rnk = 1
                       ) arrival
                           left join jms_dim.dim_network_whole_massage net
                                     on scan_site_code = net.code
                  where net.network_type = 6
                    and fenmu_tichu_flag <> 1
                  group by net.agent_code, dt
              ) merges
                  left join new_net on merges.agent_code = new_net.bs_agent_code
         group by if(new_net.bs_agent_code is not null, new_net.agent_code, merges.agent_code), dt
     ),
     merge1 as (
         select dt,
                proxy_code,
                -- max(proxy_name)         as proxy_name,
                sum(work_cnt)           as work_cnt,
                sum(project_cnt)        as project_cnt,
                sum(appeal_cnt)         as appeal_cnt,
                sum(third_platform_cnt) as third_platform_cnt,
                sum(transfer_cnt_total) as transfer_cnt_total,
                sum(claim_cnt)          as claim_cnt,
                sum(taking_cnt)         as taking_cnt,
                sum(arrival_cnt)        as arrival_cnt,
                sum(site_cnt)           as site_cnt
         from (
                  select *
                  from accept
                  union
                  select *
                  from duty
                  union
                  select *
                  from proxy_merge
              ) t
         where proxy_code is not null
         group by dt, proxy_code --, duty_proxy_code
     )
insert
overwrite
table
jms_dm.dm_waybill_customer_complaint_agg_new_dt
partition
(
dt
)
select null                                                                            as regional_code
     , null                                                                            as regional_desc
     , proxy_code
     , net1.name                                                                       as proxy_name
     , nvl(work_cnt, 0)                                                                as work_cnt      -- 呼叫中心客诉量
     , sum(nvl(work_cnt, 0)) over (partition by dt)                                    as work_total    -- 呼叫中心客诉量汇总
     , nvl(project_cnt, 0)                                                             as project_cnt   -- 平台客诉量
     , sum(nvl(project_cnt, 0)) over (partition by dt)                                 as project_total -- 平台客诉量汇总
     , nvl(appeal_cnt, 0)                                                              as appeal_cnt    -- 邮政客诉量
     , sum(nvl(appeal_cnt, 0)) over (partition by dt)                                  as appeal_total  -- 邮政客诉量汇总
     , nvl(claim_cnt, 0)                                                               as claim_cnt     -- 理赔客诉量
     , sum(nvl(claim_cnt, 0)) over (partition by dt)                                   as claim_total   -- 理赔客户量汇总
-- 第三方客诉量
--  第三方客诉率
-- 第三方客诉量汇总
-- 第三方汇总客诉率
     , nvl(third_platform_cnt, 0)                                                      as third_platform_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , nvl(third_platform_cnt, 0)
                       / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                       as third_platform_ratio
     , sum(nvl(third_platform_cnt, 0)) over (partition by dt)                          as third_platform_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , sum(nvl(third_platform_cnt, 0)) over (partition by dt)
                       / (((sum(nvl(taking_cnt, 0)) over (partition by dt)) * 0.15 +
                           (sum(nvl(arrival_cnt, 0)) over (partition by dt)) * 0.85)) * 10000, 0), 2)
                                                                                       as third_platform_total_ratio
-- 转运客诉量
-- 转运客诉率 -- 保留2位小数   转运客诉量/【（代理区收件量*15%）+（代理区到件量*85%）】*10000，保留2为小数点
     , nvl(transfer_cnt_total, 0)                                                      as transfer_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(transfer_cnt_total, 0)
                    / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                       as transfer_cnt_ratio
     , sum(nvl(transfer_cnt_total, 0)) over (partition by dt)                          as transfer_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                sum(nvl(transfer_cnt_total, 0)) over (partition by dt)
                    / (((sum(nvl(taking_cnt, 0)) over (partition by dt)) * 0.15 +
                        (sum(nvl(arrival_cnt, 0)) over (partition by dt)) * 0.85)) * 10000, 0), 2)
                                                                                       as transfer_total_ratio
-- 网点客诉量
-- 网点累计客诉量 所有代理区网点客诉量求和
-- 网点客诉率 保留2位小数 客诉率=客诉量/{(出件量*15%)+(到件量（V1.3更改）*85%)}*10000，保留2位小数点
-- 网点累计客诉率 计算公式：全代理区网点日客诉量/【全代理区（收件量*15%）+（到件量*85%）】*10000，保留2为小数点
     , nvl(site_cnt, 0)                                                                as network_cnt
     , sum(nvl(site_cnt, 0)) over (partition by dt)                                    as network_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                nvl(site_cnt, 0)
                    / ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                       as network_ratio
     , round(if(taking_cnt > 0 or arrival_cnt > 0
                 , sum(nvl(site_cnt, 0)) over (partition by dt)
                       / (((sum(nvl(taking_cnt, 0)) over (partition by dt)) * 0.15 +
                           (sum(nvl(arrival_cnt, 0)) over (partition by dt)) * 0.85)) * 10000, 0), 2)
                                                                                       as network_total_ratio

--  客诉总量=网点客诉量+第三方客诉量+转运客诉量
-- 全网客诉量
-- 全网客诉量汇总 所有代理区全网客诉量求和
-- 全网客诉率 计算公式：全网客诉量/{(出件量*15%)+(到件量（V1.3更改）*85%)}*10000，保留2位小数点 todo: 调整为4种工单的数量和
     --, nvl(site_cnt, 0) + nvl(third_platform_cnt, 0) + nvl(transfer_cnt_total, 0) as all_cnt
     , nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0) as all_cnt
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                (nvl(work_cnt, 0) + nvl(project_cnt, 0) + nvl(appeal_cnt, 0) + nvl(claim_cnt, 0)) /
                ((nvl(taking_cnt, 0) * 0.15 + nvl(arrival_cnt, 0) * 0.85)) * 10000, 0), 2)
                                                                                       as all_ratio
     , sum(nvl(site_cnt, 0)) over (partition by dt)
    + sum(nvl(third_platform_cnt, 0)) over (partition by dt)
    + sum(nvl(transfer_cnt_total, 0)) over (partition by dt)                           as all_total
     , round(if(taking_cnt > 0 or arrival_cnt > 0,
                (sum(nvl(site_cnt, 0)) over (partition by dt) + sum(nvl(third_platform_cnt, 0)) over (partition by dt)
                    + sum(nvl(transfer_cnt_total, 0)) over (partition by dt))
                    / (((sum(nvl(taking_cnt, 0)) over (partition by dt)) * 0.15 +
                        (sum(nvl(arrival_cnt, 0)) over (partition by dt)) * 0.85)) * 10000, 0), 2)
                                                                                       as all_total_ratio
-- 代理区出件量
-- 代理区到件量a
     , nvl(taking_cnt, 0)                                                              as taking_cnt
     , nvl(arrival_cnt, 0)                                                             as arrival_cnt
     , dt                                                                              as date_time     --明细的dt,是create_time,统计的是t-1的数据
     , dt
from merge1
         left join
     (
         --逻辑调整,去code,name,做网点类型限定
         select code
              , name
         from jms_dim.dim_network_whole_massage
         where network_type = 2
     ) net1
     on merge1.proxy_code = net1.code
    distribute by dt
;
